﻿/*
 XsltDb is powerful XSLT module for DotnetNuke.
 It offers safe database access, SEO-friendly AJAX support,
 visitor interactions, environment integration (dnn properties,
 request, cookie, session and form values), regular expressions, etc.

 Author:
 
    Anton Burtsev
    burtsev@yandex.ru

 Project home page: 
 
    http://xsltdb.codeplex.com
*/

using System;
using System.Net;
using System.Net.Mail;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Security;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.XPath;
using System.Configuration;
using System.Web;
using System.Web.Caching;
using DotNetNuke.Entities.Host;
using DotNetNuke.Entities.Portals;
using DotNetNuke.Entities.Users;

using System.Globalization;

//using Ionic.Zip;

namespace Findy.XsltDb
{
    public partial class Helper
    {
        private Transformer transformer;
        public Helper(Transformer transformer)
        {
            this.transformer = transformer;
        }

        public Transformer Transformer
        {
            get { return transformer; }
        }

        public object net(string proc)
        { return net(proc, new object[] { }); }

        public object net(string proc, object p1)
        { return net(proc, new object[] { p1 }); }

        public object net(string proc, object p1, object p2)
        { return net(proc, new object[] { p1, p2 }); }

        public object net(string proc, object p1, object p2, object p3)
        { return net(proc, new object[] { p1, p2, p3 }); }

        public object net(string proc, object p1, object p2, object p3, object p4)
        { return net(proc, new object[] { p1, p2, p3, p4 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5)
        { return net(proc, new object[] { p1, p2, p3, p4, p5 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5, object p6)
        { return net(proc, new object[] { p1, p2, p3, p4, p5, p6 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5, object p6, object p7)
        { return net(proc, new object[] { p1, p2, p3, p4, p5, p6, p7 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8)
        { return net(proc, new object[] { p1, p2, p3, p4, p5, p6, p7, p8 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9)
        { return net(proc, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10)
        { return net(proc, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10, object p11)
        { return net(proc, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11 }); }

        public object net(string proc, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10, object p11, object p12)
        { return net(proc, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12 }); }

        static Dictionary<string, MethodInfo> methodCache = new Dictionary<string, MethodInfo>();
        static Dictionary<string, object> objectCache = new Dictionary<string, object>();
        Dictionary<string, object> objectHandles = new Dictionary<string, object>();

        private bool isSimpleType(Type t)
        {
            if (t == typeof(Int16)) return true;
            if (t == typeof(Int32)) return true;
            if (t == typeof(Int64)) return true;

            if (t == typeof(UInt16)) return true;
            if (t == typeof(UInt32)) return true;
            if (t == typeof(UInt64)) return true;

            if (t == typeof(Double)) return true;
            if (t == typeof(Single)) return true;
            if (t == typeof(Decimal)) return true;

            if (t == typeof(Char)) return true;
            if (t == typeof(String)) return true;
            if (t == typeof(Byte)) return true;
            if (t == typeof(DateTime)) return true;

            return false;
        }

        public object netcall(string instance, string method)
        { return netcall(instance, method, new object[] { }); }

        public object netcall(string instance, string method, object p1)
        { return netcall(instance, method, new object[] { p1 }); }

        public object netcall(string instance, string method, object p1, object p2)
        { return netcall(instance, method, new object[] { p1, p2 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3)
        { return netcall(instance, method, new object[] { p1, p2, p3 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5, object p6)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5, p6 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5, object p6, object p7)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5, p6, p7 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5, p6, p7, p8 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10, object p11)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11 }); }

        public object netcall(string instance, string method, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10, object p11, object p12)
        { return netcall(instance, method, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12 }); }

        private object netcall(string instance, string method, object[] p)
        {
            return net(instance + ".." + method, p);
        }

        private object net(string proc, object[] p)
        {
            string[] parts = proc.Split('.');

            var _prms = new List<object>();
            if ( parts[1].Length > 0 )
                _prms.Add(transformer.DnnSettings.P.PortalID);
            _prms.AddRange(p);
            object[] prms = _prms.ToArray();

            string objectName = parts[0] + "." + parts[1];
            object h;
            if (parts[1].Length != 0)
            {
                if (!objectCache.ContainsKey(objectName))
                {
                    h = Activator.CreateInstance(parts[0], "Mdo.XsltDb." + parts[1]).Unwrap();
                    objectCache[objectName] = h;
                }
                else
                    h = objectCache[objectName];
            }
            else
                h = objectHandles[parts[0]];

            MethodInfo theMethod = null;
            if (!methodCache.ContainsKey(proc))
            {
                foreach (MethodInfo mi in h.GetType().GetMethods(BindingFlags.Public|BindingFlags.Instance))
                {
                    if (mi.Name != parts[2])
                        continue;

                    ParameterInfo[] pi = mi.GetParameters();

                    if (pi.Length != prms.Length)
                        continue;

                    if (theMethod != null)
                        throw new Exception("Ambigous " + proc);

                    theMethod = mi;
                }
                if (theMethod == null)
                    throw new Exception("Can't find assembly/method for " + proc);

                methodCache[proc] = theMethod;
            }
            else
                theMethod = methodCache[proc];

            ParameterInfo[] pi2 = theMethod.GetParameters();
            for (int i = 0; i < prms.Length; i++)
            {
                Type pType = pi2[i].ParameterType;
                if (isSimpleType(pType))
                    prms[i] = Convert.ChangeType(cast(prms[i]), pType);
                else
                    prms[i] = objectHandles[prms[i].ToString()];
            }

            object ret = theMethod.Invoke(h, prms);
            if (isSimpleType(ret.GetType()))
                return ret;
            string guid = Guid.NewGuid().ToString();
            objectHandles[guid] = ret;
            return guid;
        }

        static Regex UrlTestRegex = new Regex(@"^\w+://", RegexOptions.Compiled);
        static Regex XmlTestRegex = new Regex(@"^<", RegexOptions.Compiled);
        static Regex JsonTestRegex = new Regex(@"^[\{\[""]", RegexOptions.Compiled);
        static Regex QsTestRegex = new Regex(@"^\??([^=]+=[^&]*(&|$))+$", RegexOptions.Compiled);
        public XPathNavigator nodeset(object text)
        {
            return nodeset(text, 0);
        }

        public XPathNavigator nodeset(object _text, int seconds)
        {
            return nodeset(_text, seconds, null);
        }
        public XPathNavigator nodeset(object _text, int seconds, object values)
        {
            // in case of result tree fragment create new XPathDocument.
            // return (_text as XPathNavigator) also works but it seems it is not so fast as new one...
            if (_text is XPathNavigator)
            {
                var nav = _text as XPathNavigator;
                var xml = nav.OuterXml;
                if (XmlTestRegex.IsMatch(xml)) return CreateNavigator(xml);
                _text = nav.Value.Trim();
            }

            var text = cast(_text).ToString().Trim();
            try
            {
                if (text.StartsWith("~"))
                    text = FileMan.MapPath(text);
                if (XmlTestRegex.IsMatch(text))
                    return CreateNavigator(text);
                if (JsonTestRegex.IsMatch(text))
                    return JsonHelpers.Json2Xml(text);
                if (UrlTestRegex.IsMatch(text))
                {
                    var key = "ext-xml:" + text;
                    var res = (string)StaticCache2.Get(key, delegate()
                    {
                        var item = new StaticCache2.Item();
                        var r = WebRequest.Create(text);
                        if (values != null)
                        {
                            if ( values is XPathNavigator )
                            {
                                var sb = new StringBuilder();
                                foreach(XPathNavigator node in (values as XPathNavigator).Select("*"))
                                {
                                    if ( node.Name.ToLower() == "mdo:post-xml")
                                    {
                                        var encoding = Encoding.UTF8;
                                        var encodingNode = node.SelectSingleNode("@encoding");
                                        if ( encodingNode!=null)
                                        {
                                            var encodingName = encodingNode.Value;
                                            int encodingCode;
                                            encoding = int.TryParse(encodingName, out encodingCode)
                                                ? Encoding.GetEncoding(encodingCode)
                                                : Encoding.GetEncoding(encodingName);
                                        }
                                        var ms = new MemoryStream();
                                        var theNodeToPost = node.SelectSingleNode("*");
                                        if ( theNodeToPost == null )
                                            throw new NullReferenceException("Node to be sent to the '" + r.RequestUri + "' is empty.");
                                        using (var sw = new StreamWriter(ms, encoding))
                                        {
                                            using (var w = XmlWriter.Create(sw))
                                                theNodeToPost.WriteSubtree(w);
                                        }
                                        var xmlBin = ms.ToArray();

                                        r.Method = "POST";
                                        r.ContentType = "text/xml";
                                        r.ContentLength = xmlBin.Length;
                                        r.GetRequestStream().Write(xmlBin, 0, xmlBin.Length);

                                        item.item = new XPathDocument(r.GetResponse().GetResponseStream()).CreateNavigator().OuterXml;
                                        item.seconds = seconds;
                                        return item;
                                    }
                                    if (sb.Length > 0) sb.Append("&");
                                    sb.Append(urlencode(node.Name)).Append("=").Append(urlencode(node.Value));
                                }
                                values = sb.ToString();
                            }
                            var v = (string) values;
                            r.Method = "POST";
                            r.ContentType = "application/x-www-form-urlencoded";
                            r.ContentLength = v.Length;
                            r.GetRequestStream().Write(Encoding.ASCII.GetBytes(v), 0, v.Length);
                        }
                        item.item = new XPathDocument(r.GetResponse().GetResponseStream()).CreateNavigator().OuterXml;
                        item.seconds = seconds;
                        return item;
                    });
                    return new XPathDocument(new StringReader(res)).CreateNavigator();
                }
                if (QsTestRegex.IsMatch(text))
                    return Qs2Xml(text);
            }
            catch (Exception exc) { return CreateRootOnlyNavigator(exc.ToString()); }
            return CreateRootOnlyNavigator();
        }

        public XPathNavigator document(string text) { return nodeset(text); }
        public XPathNavigator document(string text, int seconds) { return nodeset(text, seconds); }

        static Regex QueryStringRegex = new Regex(@"(^|\?|&)([^=]+)=([^&]*)", RegexOptions.Singleline | RegexOptions.Compiled);
        public XPathNavigator Qs2Xml(string text)
        {
            StringWriter sw = new StringWriter();
            XmlWriterSettings s = new System.Xml.XmlWriterSettings();
            s.OmitXmlDeclaration = true;
            XmlWriter xw = XmlWriter.Create(sw, s);
            xw.WriteStartElement("root");

            foreach (Match m in QueryStringRegex.Matches(text))
            {
                xw.WriteElementString(
                    XmlConvert.EncodeLocalName(HttpUtility.UrlDecode(m.Groups[2].Value)),
                    HttpUtility.UrlDecode(m.Groups[3].Value));
            }
            xw.Close();
            return new XPathDocument(new StringReader(sw.ToString())).CreateNavigator();
        }

        public string urlastext(string uri)
        {
            HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(uri);
            WebResponse resp = httpWebRequest.GetResponse();
            using (Stream strm = resp.GetResponseStream())
            {
                StreamReader sr = new StreamReader(strm);
                return sr.ReadToEnd();
            }
        }

        public string urlastext(string uri, int seconds)
        {
            string key = "urlastext:" + (uri.Length > 128 ? uri.MD5() : uri);
            return StaticCache2.Get(key, seconds, delegate() {
                return urlastext(uri);
            }).ToString();
        }

        private static Regex sqlCacheRegex = new Regex(@"\s*#\s*(\d+)?\s*(.+)?", RegexOptions.Singleline | RegexOptions.Compiled);

        public XPathNavigator xml(string procedure, string names)
        { return xml(procedure, names, new object[] { }); }

        public XPathNavigator xml(string procedure, string names, object p1)
        { return xml(procedure, names, new object[] { p1 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2)
        { return xml(procedure, names, new object[] { p1, p2 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3)
        { return xml(procedure, names, new object[] { p1, p2, p3 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4, object p5)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4, p5 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4, object p5, object p6)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4, p5, p6 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4, object p5, object p6, object p7)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4, p5, p6, p7 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4, p5, p6, p7, p8 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10 }); }

        public XPathNavigator xml(string procedure, string names, object p1, object p2, object p3, object p4, object p5, object p6, object p7, object p8, object p9, object p10, object p11)
        { return xml(procedure, names, new object[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11 }); }


        private XPathNavigator xml(string procedure, string names, params object[] parameters)
        {
            for (int i = 0; i < parameters.Length; i++)
                parameters[i] = cast(parameters[i]);
            names = names.Trim();
            procedure = procedure.Trim();

            Match m = sqlCacheRegex.Match(names);
            if (m.Success)
            {
                names = sqlCacheRegex.Replace(names, string.Empty);

                StringBuilder sb = new StringBuilder(procedure);
                sb.Append("|");
                sb.Append(transformer.PortalID);
                sb.Append("|");
                sb.Append(names);

                foreach (object p in parameters)
                    sb.Append("|").Append(cast(p));
                string key = sb.ToString().MD5();
                return (XPathNavigator)StaticCache2.Get(key, delegate()
                {
                    CacheState cacheState = new CacheState();
                    string cacheDescriptor = null;
                    cacheState.key = key;
                    if (m.Groups[1].Success) // Caching is required
                    {
                        cacheState.seconds = int.Parse(m.Groups[1].Value);
                        cacheDescriptor = XsltDbUtils.GetCacheDescriptor(getRealProcedureName(procedure));
                        if ( !string.IsNullOrEmpty(cacheDescriptor) ) // Cache validation is required
                        {
                            if (string.Compare(cacheDescriptor, "$query", true) == 0)
                                cacheState.selfCache = true;
                        }
                    }
                    XPathNavigator result = execXml(procedure, names, cacheState, parameters);
                    if (cacheState.noCache)
                    {
                        var ci = new StaticCache2.Item();
                        ci.item = result;
                        ci.noCache = true;
                        return ci;
                    }
                    if (cacheState.selfCache)
                    {
                        var ci = new StaticCache2.Item();
                        ci.item = result;
                        ci.seconds = cacheState.seconds;
                        ci.dep = cacheState.dep;
                        return ci;
                    }
                    if (cacheState.seconds > 0) // Caching is required
                    {
                        var ci = new StaticCache2.Item();
                        ci.item = result;
                        ci.seconds = cacheState.seconds;
                        if (!string.IsNullOrEmpty(cacheDescriptor)) // Cache validation is required
                        {
                            ci.tables = createTableList(cacheDescriptor);
                            ci.parameters = createSqlParametersArray(new object[]{"@PortalID", this.transformer.DnnSettings.P.PortalID});
                            ci.dep = cacheState.dep;
                        }

                        return ci;
                    }
                    else
                        return result;
                });
            }
            else
                return execXml(procedure, names, null, parameters);
        }

        private const string prefix = "mdo_xslt_";
        private const string xmlroot = "root";
        private static string getRealProcedureName(string procedure)
        {
            return procedure[0] == ':' ? procedure.Substring(1) : prefix + procedure;
        }
        private XPathNavigator execXml(string procedure, string names, CacheState cacheState, params object[] parameters)
        {
            procedure = procedure.Replace("-", "_");
            bool IsSuper = transformer.Config.IsSuper;

            if (procedure[0] == ':' && !IsSuper)
                throw new Exception("Only superusers allowed to call ':' procedures");

            List<object> parameterList = new List<object>();
            if ( procedure[0] != ':' )
                parameterList.Add(transformer.PortalID.ToString());
            parameterList.AddRange(parameters);

            string procname = IsSuper && procedure[0] == ':' ? procedure.Substring(1) : prefix + procedure;

            // Change type to correctly handle result of xslt-built stuff
            for (int i = 1; i < parameterList.Count; i++)
                parameterList[i] = cast(parameterList[i]);

            if (names == "$xml")
            {
                return Data.SP2XML(procname, cacheState, parameterList.ToArray());
            }
            else if (names == "$scalar")
            {
                using (IDataReader dr = Data.ExecuteReader(procname, cacheState, parameterList.ToArray()))
                    if (dr.Read())
                        return CreateScalarNavigator(dr[0].ToString());
                return CreateRootOnlyNavigator();
            }
            else if (names == "$jsonrow")
            {
                using (IDataReader reader = Data.ExecuteReader(procname, cacheState, parameterList.ToArray()))
                    return GetJsonRowFromReader(reader);
            }
            else if (names.StartsWith("$json"))
            {
                using (IDataReader reader = Data.ExecuteReader(procname, cacheState, parameterList.ToArray()))
                    return GetJsonFromReader(reader, names.Replace("$json", "").Trim());
            }
            else
            {
                using (IDataReader reader = Data.ExecuteReader(procname, cacheState, parameterList.ToArray()))
                    return GetXmlFromReader(reader, names);
            }
        }

        static private XPathNavigator CreateNavigator(XmlReader reader)
        {
            return new XPathDocument(reader).CreateNavigator();
        }
        static private XPathNavigator CreateNavigator(string xml)
        {
            return new XPathDocument(new StringReader(xml)).CreateNavigator();
        }
        static private XPathNavigator CreateRootOnlyNavigator()
        {
            return CreateRootOnlyNavigator(string.Empty);
        }
        static private XPathNavigator CreateScalarNavigator(string value)
        {
            return CreateNavigator("<root><value v=\"" + HttpUtility.HtmlAttributeEncode(value) + "\" /></root>").SelectSingleNode("//value/@v");
        }
        static private XPathNavigator CreateRootOnlyNavigator(string text)
        {
            return new XPathDocument(
                new StringReader("<root>" + HttpUtility.HtmlEncode(text) + "</root>")
                ).CreateNavigator();
        }

        public string text(object data)
        {
            return ObjectToText(data, false);
        }

        public string html(object data)
        {
            string str = text(data);

            Regex rx = new Regex("\\n");
            return rx.Replace(HttpUtility.HtmlEncode(str).Replace(" ", "&nbsp;").Replace("\t", "&nbsp;&nbsp;&nbsp;"), "<br />");
        }

        private static string ObjectToText(object data, bool wrap)
        {
            // wrap = false   means that we must NOT wrap even having multiple roots
            // wrap = true    means that we must wrap ONLY having multiple roots


            if (data is XPathNodeIterator)
            {
                StringBuilder sb = new StringBuilder();
                XPathNodeIterator it = data as XPathNodeIterator;
                foreach (XPathNavigator navi in it)
                    sb.Append(navi.OuterXml);
                if (wrap && it.Count > 1)
                    return "<root>" + sb.ToString() + "</root>";
                return sb.ToString();
            }
            else if (data is XPathNavigator)
            {
                XPathNavigator navi = data as XPathNavigator;
                if ( wrap && (navi.Select("/*").Count > 1))
                    return "<root>" + navi.OuterXml + "</root>";
                return navi.OuterXml;
            }
            else
                return wrap ? "<root>" + Transformer.XmlEncode(data.ToString()) + "</root>" : data.ToString(); 
        }

        public bool save(string procedure, object data)
        {
            return save(procedure, transformer.PortalID, data);
        }
        public bool save(string procedure, int PortalID, object data)
        {
            if (data == null)
                return true;

            procedure = procedure.Replace("-", "_");
            procedure = procedure.Trim();

            bool IsSuper = transformer.Config.IsSuper;

            if ( !IsSuper && PortalID != transformer.PortalID )
                throw new Exception("You only allowed to access portal #" + transformer.DnnSettings.P.PortalID);

            if (procedure[0] == ':' && !IsSuper)
                throw new Exception("Only superusers allowed to call ':' procedures");

            string procname = IsSuper && procedure[0] == ':' ? procedure.Substring(1) : prefix + procedure;

            try
            {
                Data.ExecuteNonQuery(procname, new object[]{PortalID, text(data)});
                return true;
            }

            catch (Exception ex)
            {
                ex.ToString();
                return false;
            }
        }

        static Regex SqlNotificationsRegex = new Regex(@"
[^,\(\s]+
(\(
            [^\(\)]*
            (
                        (
                                    (?<Open>\()
                                    [^\(\)]*
                        )+
                        (
                                    (?<Close-Open>\))
                                    [^\(\)]*
                        )+
            )*
            (?(Open)(?!))
\))?
", RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled);
        private string[] createTableList(string tables)
        {
            // This balanced groups match matching outermost brackets.

            List<string> t = new List<string>();
            foreach (Match m in SqlNotificationsRegex.Matches(tables))
                t.Add(dnnpreparesql(m.Value.Trim()).Trim());
            return t.ToArray();
        }

        public XPathNavigator sql(string sql, string names)
        { return execSql(sql, names); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1)
        { return execSql(sql, names, pn1, pv1); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2)
        { return execSql(sql, names, pn1, pv1, pn2, pv2); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5, string pn6, object pv6)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5, pn6, pv6); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5, string pn6, object pv6, string pn7, object pv7)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5, pn6, pv6, pn7, pv7); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5, string pn6, object pv6, string pn7, object pv7, object pn8, object pv8)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5, pn6, pv6, pn7, pv7, pn8, pv8); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5, string pn6, object pv6, string pn7, object pv7, object pn8, object pv8, object pn9, object pv9)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5, pn6, pv6, pn7, pv7, pn8, pv8, pn9, pv9); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5, string pn6, object pv6, string pn7, object pv7, object pn8, object pv8, object pn9, object pv9, object pn10, object pv10)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5, pn6, pv6, pn7, pv7, pn8, pv8, pn9, pv9, pn10, pv10); }

        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5, string pn6, object pv6, string pn7, object pv7, object pn8, object pv8, object pn9, object pv9, object pn10, object pv10, object pn11, object pv11)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5, pn6, pv6, pn7, pv7, pn8, pv8, pn9, pv9, pn10, pv10, pn11, pv11); }
        public XPathNavigator sql(string sql, string names, string pn1, object pv1, string pn2, object pv2, string pn3, object pv3, string pn4, object pv4, string pn5, object pv5, string pn6, object pv6, string pn7, object pv7, object pn8, object pv8, object pn9, object pv9, object pn10, object pv10, object pn11, object pv11, object pn12, object pv12)
        { return execSql(sql, names, pn1, pv1, pn2, pv2, pn3, pv3, pn4, pv4, pn5, pv5, pn6, pv6, pn7, pv7, pn8, pv8, pn9, pv9, pn10, pv10, pn12, pv12); }

        public class CacheState
        {
            public bool noCache = false;
            public string privateConnection = null;
            public string key;
            public int seconds = 0;
            public bool selfCache = false;
            public SqlCacheDependency dep = null;
        }
        private XPathNavigator execSql(string sql, string names, params object[] parameters)
        {
            for (int i = 0; i < parameters.Length; i++)
                parameters[i] = cast(parameters[i]);
            sql = sql.Trim();
            names = names.Trim();

            Match m = sqlCacheRegex.Match(names);
            if (m.Success)
            {
                names = sqlCacheRegex.Replace(names, string.Empty);

                StringBuilder sb = new StringBuilder(sql);
                sb.Append(names);

                foreach(object p in parameters)
                    sb.Append(p).Append("|");
                string key = sb.ToString().MD5();
                return (XPathNavigator)StaticCache2.Get(key, delegate() {
                    CacheState cacheState = new CacheState();
                    cacheState.key = key;
                    if (m.Groups[1].Success) // Caching is required
                    {
                        cacheState.seconds = int.Parse(m.Groups[1].Value);
                        if (m.Groups[2].Success) // Cache validation is required
                        {
                            var tables = m.Groups[2].Value.Trim();
                            if ( string.Compare(tables, "$query", true) == 0 )
                                cacheState.selfCache = true;
                        }
                    }

                    XPathNavigator result = makeRealQuery(sql, names, cacheState, parameters);
                    if (cacheState.noCache)
                    {
                        StaticCache2.Item ci = new StaticCache2.Item();
                        ci.item = result;
                        ci.noCache = true;
                        return ci;
                    }
                    if (cacheState.selfCache)
                    {
                        StaticCache2.Item ci = new StaticCache2.Item();
                        ci.item = result;
                        ci.seconds = cacheState.seconds;
                        ci.dep = cacheState.dep;
                        return ci;
                    }
                    if (cacheState.seconds > 0) // Caching is required
                    {
                        StaticCache2.Item ci = new StaticCache2.Item();
                        ci.item = result;
                        ci.seconds = cacheState.seconds;
                        if (m.Groups[2].Success) // Cache validation is required
                        {
                            ci.tables = createTableList(m.Groups[2].Value);
                            ci.parameters = createSqlParametersArray(parameters);
                            ci.privateConnection = cacheState.privateConnection;
                            ci.dep = cacheState.dep;
                        }

                        return ci;
                    }
                    else
                        return result;
                });
            }
            else
                return makeRealQuery(sql, names, null, parameters);
        }

        private XPathNavigator makeRealQuery(string sql, string names, CacheState cacheState, params object[] parameters)
        {
            try
            {
                XsltDbUtils.aConfig config = transformer.Config;
                if (sql[0] == ':' && !config.IsSuper)
                    throw new Exception("You have no permission to execute free sql statements");

                XsltDbPortalSettings settings = XsltDbUtils.GetPortalSettings(transformer.PortalID);

                if (sql[0] != ':' && settings != null && settings.ConnectionString != null
                    && settings.ConnectionString.Length > 0)
                {
                    using (new Impersonator(settings.UserName, settings.DomainName, settings.Password))
                    {
                        if (cacheState != null)
                            cacheState.privateConnection = settings.ConnectionString;
                        return doExecSql(sql, names, parameters, settings.ConnectionString, cacheState);
                    }
                }
                else
                {
                  if (config == null || config.IsSuper) // Only supermodules can access DNN database
                        return doExecSql(sql[0] == ':' ? sql.Substring(1) : sql, names, parameters, null, cacheState);
                    return CreateRootOnlyNavigator("Only Super Modules can access DNN database.");
                }
            }
            catch (SecurityException sex)
            {
                throw new Exception(@"

THERE IS NO PERMISSION TO IMPERSONATE.
Check 'trust' attribute in web.config. Basicly, it may look like '<trust level=""Full"" />'.
Or, you can setup only unmanaged call permission. http://msdn.microsoft.com/en-us/library/wyts434y.aspx

", sex);
            }
            catch (Exception ex)
            {
                if ( cacheState != null )
                    cacheState.noCache = true;
                return CreateRootOnlyNavigator(ex.Message);
            }
        }

        private static SqlParameter[] createSqlParametersArray(object[] parameters)
        {
            var prms = new List<SqlParameter>();
            for (var i = 0; i < parameters.Length; i += 2)
                prms.Add(new SqlParameter(cast(parameters[i]).ToString(), cast(parameters[i + 1])));
            return prms.ToArray();
        }

        private static XPathNavigator doExecSql(string sql, string names, object[] parameters, string cs, CacheState cacheState)
        {
            if (names == "$xml")
                return Data.SQL2XML(sql, cs, cacheState, createSqlParametersArray(parameters));

            using (IDataReader dr = Data.ExecuteSQL(sql, cacheState, cs, createSqlParametersArray(parameters)))
            {
                if (names == "$scalar")
                {
                    if (dr.Read())
                        return CreateScalarNavigator(dr[0].ToString());
                    return CreateRootOnlyNavigator();
                }
                if (names == "$script")
                    return CreateRootOnlyNavigator("ok");
                else if (names == "$jsonrow")
                    return GetJsonRowFromReader(dr);
                else if (names.StartsWith("$json"))
                    return GetJsonFromReader(dr, names.Replace("$json", "").Trim());
                else
                    return GetXmlFromReader(dr, names);
            }
        }

        internal static XPathNavigator GetJsonFromReader(IDataReader reader, string names)
        {
            var ds = new DataSet(xmlroot);
            var narray = names.Replace(" ", "").Split(',');
            ds.Load(reader, LoadOption.OverwriteChanges, narray);
            return CreateScalarNavigator(JsonHelpers.ConvertDataSetToDic(ds).xJson());
        }
        internal static XPathNavigator GetJsonRowFromReader(IDataReader reader)
        {
            string json = "{}";
            if (reader.Read())
            {
                var row = new Dictionary<string,object>();
                for (var i = 0; i < reader.FieldCount; i++)
                    row[reader.GetName(i)] = reader[i];
                json = row.xJson();
            }
            return CreateScalarNavigator(json);
        }
        internal static XPathNavigator GetXmlFromReader(IDataReader reader, string names)
        {
            var ds = new DataSet(xmlroot);
            var narray = names.Replace(" ", "").Split(',');
            ds.Load(reader, LoadOption.OverwriteChanges, narray);

            return CreateNavigator(ds.GetXml());
        }


        public string watch(string pname)
        {
            return "watch-" + pname + "-" + transformer.ClientID;
        }

        public bool isinrole(string role)
        {
            if (transformer.UserID == -1)
                return false;
            return transformer.DnnSettings.U.IsInRole(role);
        }

        public bool isinrole(string role, int UserID)
        {
            UserInfo ui = UserController.GetUserById(transformer.PortalID, UserID);
            if (ui == null)
                return false;
            return ui.IsInRole(role);
        }

        public bool issuperuser()
        {
            if (transformer.UserID == -1)
                return false;
            return transformer.DnnSettings.U.IsSuperUser;
        }

        public decimal max(decimal x, decimal y)
        {
            return Math.Max(x, y);
        }
        public decimal min(decimal x, decimal y)
        {
            return Math.Min(x, y);
        }

        public XPathNodeIterator sequence(int count)
        {
            return sequence(0, count - 1, 1);
        }
        public XPathNodeIterator sequence(decimal from, decimal to, decimal step)
        {
            StringBuilder sb = new StringBuilder("<items>");
            for (decimal x = from; x <= to; x += step)
            {
                sb.Append("<item>");
                sb.Append(x);
                sb.Append("</item>");
            }
            sb.Append("</items>");
            return CreateNavigator(sb.ToString()).Select("//item/text()");
        }
        public XPathNodeIterator split(string str, string pattern)
        {
            Regex r = new Regex(pattern, RegexOptions.Singleline);
            string[] items = r.Split(str);
            XmlDocument doc = new XmlDocument();
            XmlElement xItems = doc.CreateElement("items");
            foreach (string item in items)
            {
                XmlElement xItem = doc.CreateElement("item");
                xItem.InnerText = item;
                xItems.AppendChild(xItem);
            }

            doc.AppendChild(xItems);
            return doc.CreateNavigator().Select("//item/text()");
        }

        public bool ismatch(string str, string pattern)
        {
            Regex r = new Regex(pattern, RegexOptions.Singleline);
            return r.IsMatch(str);
        }
        public XPathNodeIterator match(string str, string pattern)
        {
            Regex r = new Regex(pattern, RegexOptions.Singleline);
            XmlDocument doc = new XmlDocument();
            XmlElement xItems = doc.CreateElement("matches");
            foreach (Match m in r.Matches(str))
            {
                XmlElement xItem = doc.CreateElement("match");
                int n = 0;
                foreach (Group g in m.Groups)
                {
                    if (g.Success)
                    {
                        XmlElement xGroup = doc.CreateElement("group");
                        XmlAttribute xGroupN;

                        xGroupN = doc.CreateAttribute("n");
                        xGroupN.Value = n.ToString();
                        xGroup.Attributes.Append(xGroupN);

                        xGroupN = doc.CreateAttribute("name");
                        xGroupN.Value = r.GroupNameFromNumber(n);
                        xGroup.Attributes.Append(xGroupN);

                        xGroup.InnerText = g.Value;
                        xItem.AppendChild(xGroup);
                    }
                    n++;
                }
                xItems.AppendChild(xItem);
            }

            doc.AppendChild(xItems);
            return doc.CreateNavigator().Select("/");
        }

        public object iif(bool selector, object v1, object v2)
        {
            if (selector)
                return v1;
            return v2;
        }
        public object coalesce(object v1, object v2)
        {
            if (v1 == null || cast(v1).ToString().Length == 0)
                return v2;
            return v1;
        }
        public object coalesce(object v1, object v2, object v3)
        { return coalesce(coalesce(v1, v2), v3); }
        public object coalesce(object v1, object v2, object v3, object v4)
        { return coalesce(coalesce(coalesce(v1, v2), v3), v4); }

        public object coalescenull(object nv, object v1, object v2)
        {
            string snv = (cast(nv) ?? "").ToString();
            string sv1 = (cast(v1) ?? "").ToString();

            if (sv1 == snv)
                return v2;
            return v1;
        }
        public object coalescenull(object nv, object v1, object v2, object v3)
        { return coalescenull(nv, coalescenull(nv, v1, v2), v3); }
        public object coalescenull(object nv, object v1, object v2, object v3, object v4)
        { return coalescenull(nv, coalescenull(nv, coalescenull(nv, v1, v2), v3), v4); }

        public int tabid(string marker, string where)
        {
            string fieldName;
            switch(where)
            {
                case "name": fieldName = "TabName"; break;
                case "title": fieldName = "Title"; break;
                case "desc": fieldName = "Description"; break;
                case "kw": fieldName = "KeyWords"; break;
                case "head": fieldName = "PageHeaderText"; break;
                default: return -1;
            }
            using (IDataReader r = Data.ExecuteSQL(
                "select * from {databaseOwner}[{objectQualifier}Tabs] where " + fieldName + " like '%' + @Marker + '%%' and PortalID = @PortalID",
                new SqlParameter[] {
                    new SqlParameter("@Marker", marker),
                    new SqlParameter("@PortalID", transformer.DnnSettings.P.PortalID)
                }))
            {
                if (r != null && r.Read())
                    return Convert.ToInt32(r["TabID"]);
            }
            return -1;
        }

        public XPathNavigator tabswithconfig(string guid)
        {
            string sql = @"
select t.* from {databaseOwner}[{objectQualifier}Tabs] t
join {databaseOwner}[{objectQualifier}TabModules] tm
    on t.TabID = tm.TabID
join {databaseOwner}[{objectQualifier}Findy_XsltDb_Modules] m
    on m.ModuleID = tm.ModuleID
where m.ConfigID = @ConfigID
and t.PortalID = @PortalID;
";
            return Data.ExecuteSQL(sql, "tab",
                new SqlParameter[]{
                    new SqlParameter("@ConfigID", guid),
                    new SqlParameter("@PortalID", transformer.PortalID)
                });
        }
/*
        public string friendlyurl(int tabid, string path)
        {
            foreach (TabInfo ti in TabController.GetPortalTabs(transformer.PortalID, -1, true, true))
                if (ti.TabID == tabid)
                    return Globals.FriendlyUrl(null, ti.TabPath);
            return "/tabid/"+tabid+"/default.aspx";
        }
        */


        public object dnn(string name)
        {
            return ReflectionTools.GetPropertyValue(transformer.DnnSettings, name);
        }

        public object dnn(string name, object defaultValue)
        {
            object v = ReflectionTools.GetPropertyValue(transformer.DnnSettings, name);
            if (v == null || v == string.Empty)
                return defaultValue;
            return v;
        }

        public string submit()
        { return perform("submit", null, new string[] { }, new string[] { }); }
        public string submit(string n1, string v1)
        { return perform("submit", null, new string[] { n1 }, new string[] { v1 }); }
        public string submit(string n1, string v1, string n2, string v2)
        { return perform("submit", null, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string submit(string n1, string v1, string n2, string v2, string n3, string v3)
        { return perform("submit", null, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string submit(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return perform("submit", null, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string submit(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return perform("submit", null, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        public string submit(string validator)
        { return perform("submit", validator, new string[] { }, new string[] { }); }
        public string submit(string validator, string n1, string v1)
        { return perform("submit", validator, new string[] { n1 }, new string[] { v1 }); }
        public string submit(string validator, string n1, string v1, string n2, string v2)
        { return perform("submit", validator, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string submit(string validator, string n1, string v1, string n2, string v2, string n3, string v3)
        { return perform("submit", validator, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string submit(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return perform("submit", validator, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string submit(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return perform("submit", validator, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        public string jsubmit()
        { return "javascript:" + perform("submit", null, new string[] { }, new string[] { }); }
        public string jsubmit(string n1, string v1)
        { return "javascript:" + perform("submit", null, new string[] { n1 }, new string[] { v1 }); }
        public string jsubmit(string n1, string v1, string n2, string v2)
        { return "javascript:" + perform("submit", null, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string jsubmit(string n1, string v1, string n2, string v2, string n3, string v3)
        { return "javascript:" + perform("submit", null, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string jsubmit(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return "javascript:" + perform("submit", null, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string jsubmit(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return "javascript:" + perform("submit", null, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        public string jsubmit(string validator)
        { return "javascript:" + perform("submit", validator, new string[] { }, new string[] { }); }
        public string jsubmit(string validator, string n1, string v1)
        { return "javascript:" + perform("submit", validator, new string[] { n1 }, new string[] { v1 }); }
        public string jsubmit(string validator, string n1, string v1, string n2, string v2)
        { return "javascript:" + perform("submit", validator, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string jsubmit(string validator, string n1, string v1, string n2, string v2, string n3, string v3)
        { return "javascript:" + perform("submit", validator, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string jsubmit(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return "javascript:" + perform("submit", validator, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string jsubmit(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return "javascript:" + perform("submit", validator, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        public string ajax()
        { return perform("ajax", null, new string[] { }, new string[] { }); }
        public string ajax(string n1, string v1)
        { return perform("ajax", null, new string[] { n1 }, new string[] { v1 }); }
        public string ajax(string n1, string v1, string n2, string v2)
        { return perform("ajax", null, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string ajax(string n1, string v1, string n2, string v2, string n3, string v3)
        { return perform("ajax", null, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string ajax(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return perform("ajax", null, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string ajax(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return perform("ajax", null, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        public string jajax()
        { return "javascript:" + perform("ajax", null, new string[] { }, new string[] { }); }
        public string jajax(string n1, string v1)
        { return "javascript:" + perform("ajax", null, new string[] { n1 }, new string[] { v1 }); }
        public string jajax(string n1, string v1, string n2, string v2)
        { return "javascript:" + perform("ajax", null, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string jajax(string n1, string v1, string n2, string v2, string n3, string v3)
        { return "javascript:" + perform("ajax", null, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string jajax(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return "javascript:" + perform("ajax", null, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string jajax(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return "javascript:" + perform("ajax", null, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        public string ajax(string validator)
        { return perform("ajax", validator, new string[] { }, new string[] { }); }
        public string ajax(string validator, string n1, string v1)
        { return perform("ajax", validator, new string[] { n1 }, new string[] { v1 }); }
        public string ajax(string validator, string n1, string v1, string n2, string v2)
        { return perform("ajax", validator, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string ajax(string validator, string n1, string v1, string n2, string v2, string n3, string v3)
        { return perform("ajax", validator, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string ajax(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return perform("ajax", validator, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string ajax(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return perform("ajax", validator, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        public string jajax(string validator)
        { return "javascript:" + perform("ajax", validator, new string[] { }, new string[] { }); }
        public string jajax(string validator, string n1, string v1)
        { return "javascript:" + perform("ajax", validator, new string[] { n1 }, new string[] { v1 }); }
        public string jajax(string validator, string n1, string v1, string n2, string v2)
        { return "javascript:" + perform("ajax", validator, new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string jajax(string validator, string n1, string v1, string n2, string v2, string n3, string v3)
        { return "javascript:" + perform("ajax", validator, new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string jajax(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return "javascript:" + perform("ajax", validator, new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string jajax(string validator, string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return "javascript:" + perform("ajax", validator, new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        internal string perform(string action, string validator, string[] names, string[] vals)
        {
            return perform(action, validator, names, vals, true);
        }
        //public string perform_encode(string s, bool attribute)
        //{
        //    s = Transformer.EncodeJsString(s);
        //    if ( attribute ) s = HttpUtility.HtmlAttributeEncode(s);
        //    return s;
        //}
        internal string perform(string action, string validator, string[] names, string[] vals, bool attribute)
        {
            List<string> pairs = new List<string>();
            for (int i = 0; i < names.Length; i++)
            {
                if (vals[i].StartsWith("@"))
                    pairs.Add(string.Format("{0} : {1}",
                        Transformer.EncodeJsString(names[i]),
                        vals[i].Substring(1)));
                else
                    pairs.Add(string.Format("{0} : {1}",
                        Transformer.EncodeJsString(names[i]),
                        Transformer.EncodeJsString(vals[i])));
            }

            return string.Format(
                "mdo_{0}({1}, {{{2}}}, {3});",
                action,
                Transformer.EncodeJsString(transformer.ClientID),
                string.Join(",", pairs.ToArray()),
                validator == null ? "null" : validator);
        }

        public string navigate(string n1, string v1)
        { return navigate(new string[] { n1 }, new string[] { v1 }); }
        public string navigate(string n1, string v1, string n2, string v2)
        { return navigate(new string[] { n1, n2 }, new string[] { v1, v2 }); }
        public string navigate(string n1, string v1, string n2, string v2, string n3, string v3)
        { return navigate(new string[] { n1, n2, n3 }, new string[] { v1, v2, v3 }); }
        public string navigate(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4)
        { return navigate(new string[] { n1, n2, n3, n4 }, new string[] { v1, v2, v3, v4 }); }
        public string navigate(string n1, string v1, string n2, string v2, string n3, string v3, string n4, string v4, string n5, string v5)
        { return navigate(new string[] { n1, n2, n3, n4, n5 }, new string[] { v1, v2, v3, v4, v5 }); }

        private string navigate(string[] pname, string[] pvalue)
        {
            string url = HttpContext.Current.Request["XsltDb_js_window_location"] ?? HttpContext.Current.Request.RawUrl;
            string rawQs = "";
            if ( url.Contains("?") )
                rawQs = url.Split('?')[1];

            MdoQueryString qs = new MdoQueryString(rawQs); // Normallize query string
            qs = new MdoQueryString(qs.ToString(false)); // Remove commands
            if (!transformer.IsNavigate)
            {
                qs.Append(XsltDbUtils.GetQSBySlot(XsltDbUtils.globalControlName).ParamsToUrl(false));
                Regex r = new Regex("^mdo-hid-(.+?)-value$");
                foreach (string key in HttpContext.Current.Request.Form.AllKeys)
                {
                    Match m = r.Match(key);
                    if ( m.Success )
                    {
                        string tm = m.Groups[1].Value;
                        qs.Append(XsltDbUtils.GetQSByClientID(tm).ParamsToUrl(tm, false));
                    }
                }
            }

            if (url.Contains("?"))
            {
                string[] parts = url.Split('?');
                url = parts[0];
                qs.Append(parts[1]);
            }

            MdoQueryString paramQs = new MdoQueryString();

            for (int i = 0; i < pname.Length; i++)
                paramQs[pname[i]] = pvalue[i];
            qs.Append(paramQs.ParamsToUrl(transformer.ClientID, false));

            string urlLower = url.ToLower();

            foreach (string key in new List<string>(qs.AllKeys))
                if (qs[key].Length == 0 || urlLower.Contains(key.ToLower() + "/" + qs[key].ToLower()))
                    qs.Remove(key);

            if (!urlLower.EndsWith("/default.aspx"))
                qs.Remove("TabId");

            foreach (string key in new List<string>(qs.AllKeys))
            {
                Regex r = new Regex("/" + regexencode(key) + "/(.+?)/", RegexOptions.Singleline);
                url = r.Replace(url,"/" + key + "/" + HttpUtility.UrlEncode(qs[key]) + "/");
            }

            string strQs = qs.ToString();
            if (strQs.Trim().Length > 0)
                url += "?" + strQs;
            return url;
        }

        public object param(string name)
        {
            return param(name, "");
        }

        List<string> requestedParams = new List<string>();
        public object param(string name, object dflt)
        {
            string url = HttpContext.Current.Request["XsltDb_js_window_location"] ?? HttpContext.Current.Request.RawUrl;

            if (name.StartsWith("$"))
            {
                string p = name.Substring(1);
                if (!requestedParams.Contains(p))
                    requestedParams.Add(p);

                if (transformer.IsNavigate)
                {
                    Regex r = new Regex(regexencode(p)+"=(.*?)(&|$)", RegexOptions.Singleline);
                    Match m = r.Match(url);
                    if ( m.Success )
                    {
                        string v = HttpUtility.UrlDecode(m.Groups[1].Value);
                        if ( v.Trim().Length > 0 )
                            return v;
                        return dflt;
                    }
                    r = new Regex("/" + regexencode(p) + "/(.+?)/", RegexOptions.Singleline);
                    m = r.Match(url);
                    if (m.Success)
                    {
                        string v = HttpUtility.UrlDecode(m.Groups[1].Value);
                        if (v.Trim().Length > 0)
                            return v;
                        return dflt;
                    }
                    return request(p, dflt);
                }
            }
            else
                if (transformer.IsNavigate)
                    return request(name + "-" + transformer.TabModuleID, dflt);

            string slot;
            if (name.StartsWith("$"))
                slot = "XsltDbGlobals";
            else
                slot = XsltDbUtils.GetHidName(transformer.ClientID);

            MdoQueryString qs = new MdoQueryString(request(slot, string.Empty).ToString());
            if ( new List<string>(qs.AllKeys).Contains(name) )
                return qs[name, dflt];

            if (name.StartsWith("$"))
            {
                string p = name.Substring(1);

                Regex r = new Regex(regexencode(p) + "=(.*?)(&|$)", RegexOptions.Singleline);
                Match m = r.Match(url);
                if (m.Success)
                {
                    string v = HttpUtility.UrlDecode(m.Groups[1].Value);
                    if (v.Trim().Length > 0)
                        return v;
                    return dflt;
                }
                r = new Regex("/" + regexencode(p) + "/(.+?)/", RegexOptions.Singleline);
                m = r.Match(url);
                if (m.Success)
                {
                    string v = HttpUtility.UrlDecode(m.Groups[1].Value);
                    if (v.Trim().Length > 0)
                        return v;
                    return dflt;
                }
            }

            return dflt;
        }

        internal string getWatchers()
        {
            // This is direct configuration call via ws.aspx
            if ( transformer.IsSystem )
                return string.Empty;

            string w = "";
            foreach (string pName in requestedParams)
                w += @"<span id=""" + watch(pName) + @""" style=""display:none;"" ></span>";

            return w;
        }

        public string redirect()
        {
            throw new RedirectException();
        }
        public string redirect(string url)
        {
            throw new RedirectException(url);
        }
        public string transfer(string url)
        {
            throw new RedirectException(url, true);
        }

        public object session(string name)
        {
            return session(name, "");
        }
        public object session(string name, object dflt)
        {
            if (HttpContext.Current == null || HttpContext.Current.Session == null)
                return string.Empty;

            string ret = (HttpContext.Current.Session[name] ?? "").ToString();
            if (ret.Length == 0)
                return dflt;
            return ret;
        }

        public object aspnet(string name)
        {
            if (name.StartsWith("Context."))
                return ReflectionTools.GetPropertyValue(HttpContext.Current, name.Substring(8));

            if (name.StartsWith("Request.Url."))
                return ReflectionTools.GetPropertyValue(HttpContext.Current.Request.Url, name.Substring(12));

            if (name.StartsWith("Request."))
                return ReflectionTools.GetPropertyValue(HttpContext.Current.Request, name.Substring(8));

            if (name.StartsWith("Module."))
                return ReflectionTools.GetPropertyValue(transformer.Module, name.Substring(7));

            if (name.StartsWith("Page."))
                return ReflectionTools.GetPropertyValue(HttpContext.Current.Handler, name.Substring(5));

            if (name.StartsWith("#"))
            {
                string id = name.Substring(1, name.IndexOf(".")-1);
                string property = name.Substring(id.Length+2);
                return ReflectionTools.GetPropertyValue(transformer.TrueHost.FindControl(id), property);
            }

            return string.Empty;
        }


        public object assign(string name, object value)
        {
            if (name.StartsWith("Context."))
                ReflectionTools.SetPropertyValue(HttpContext.Current, name.Substring(8), cast(value));

            if (name.StartsWith("Request."))
                ReflectionTools.SetPropertyValue(HttpContext.Current.Request, name.Substring(8), cast(value));

            if (name.StartsWith("Response."))
                ReflectionTools.SetPropertyValue(HttpContext.Current.Response, name.Substring(9), cast(value));

            if (name.StartsWith("Module."))
                ReflectionTools.SetPropertyValue(transformer.Module, name.Substring(7), cast(value));

            if (name.StartsWith("Page."))
                ReflectionTools.SetPropertyValue(HttpContext.Current.Handler,
                    typeof(DotNetNuke.Framework.CDefault), name.Substring(5), cast(value));

            if (name.StartsWith("#"))
            {
                string id = name.Substring(1, name.IndexOf(".") - 1);
                string property = name.Substring(id.Length + 2);
                ReflectionTools.SetPropertyValue(transformer.TrueHost.FindControl(id), property, cast(value));
            }

            return string.Empty;
        }

        public string request()
        {
            string q = querystring();
            string f = form();

            if (q.Length > 0 && f.Length > 0)
                return q + "&" + f;
            if (q.Length > 0)
                return q;
            if (f.Length > 0)
                return f;
            return string.Empty;

        }
        public object request(string name)
        {
            return request(name, "");
        }
        public object request(string name, object dflt)
        {
            string ret = (HttpContext.Current.Request[name] ?? "").ToString();
            if (ret.Length == 0)
                return dflt;
            return ret;
        }

        public string form()
        {
            return HttpContext.Current.Request.Form.ToString();
        }
        public object form(string name)
        {
            return form(name, "");
        }
        public object form(string name, object dflt)
        {
            string ret = (HttpContext.Current.Request.Form[name] ?? "").ToString();
            if (ret.Length == 0)
                return dflt;
            return ret;
        }

        public string querystring()
        {
            return HttpContext.Current.Request.QueryString.ToString();
        }
        public object querystring(string name)
        {
            return querystring(name, "");
        }
        public object querystring(string name, object dflt)
        {
            string ret = (HttpContext.Current.Request.QueryString[name] ?? "").ToString();
            if (ret.Length == 0)
                return dflt;
            return ret;
        }

        public string mappath(string relativePath)
        {
            if (relativePath.StartsWith(":"))
                return FileMan.PathCombine(HttpContext.Current.Request.PhysicalApplicationPath, relativePath.Substring(1));
            return FileMan.PathCombine(transformer.DnnSettings.P.HomeDirectoryMapPath, relativePath);
        }
        public string mappath(string relativeFolder, string relativePath)
        {
            string absoluteFolder;
            if (relativeFolder.StartsWith(":"))
                absoluteFolder = FileMan.PathCombine(HttpContext.Current.Request.PhysicalApplicationPath, relativeFolder);
            else
                absoluteFolder = FileMan.PathCombine(transformer.DnnSettings.P.HomeDirectoryMapPath, relativeFolder);
            return FileMan.PathCombine(absoluteFolder, relativePath);
        }

        private string getFilesRoot()
        {
            return getFilesRoot(transformer.PortalID);
        }

        public static string getFilesRoot(int PortalID)
        {
            return StaticCache2.Get("XsltDb:getFilesRoot." + PortalID, delegate() { 
                string root = ConfigurationManager.AppSettings["XsltDbFiles"] ?? string.Empty;
                string home = getPortalHome(PortalID);

                if (root.Length == 0)
                    root = Path.Combine(home, "XsltDbFiles");

                if (root.IndexOf("{0}") >= 0)
                    root = string.Format(root, home);

                return root;
            }).ToString();
        }

        public static string getPortalHome(int PortalID)
        {
            string home;
            PortalSettings ps = PortalController.GetCurrentPortalSettings();
            if (ps != null && ps.PortalId == PortalID)
                home = ps.HomeDirectoryMapPath.Substring(FileMan.AppPath.Length);
            else
                home = new PortalController().GetPortal(PortalID).HomeDirectory;
            return home.Replace("/", "\\");
        }

        private FileMan CreateFileManager()
        {
            return new FileMan(getFilesRoot());
        }

        public string deletefile(string path)
        {
            string ext = Path.GetExtension(path);
            FileMan m = CreateFileManager();
            string fullPath = m.FullName(path);

            // First delete file from db to prevent new requests for the file.
            Data.ExecuteNonQuery("Findy_XsltDb_Files_Delete",
                new object[]{transformer.DnnSettings.P.PortalID, path});

            // now try to delete file and retry if it is locked.
            int i = 0;
            while (i < 5)
            {
                try
                {
                    if ( File.Exists(fullPath) )
                        File.Delete(fullPath);
                    if (Directory.Exists(fullPath + ".unzipped"))
                        Directory.Delete(fullPath + ".unzipped", true);
                    if (Directory.Exists(fullPath + ".thumb"))
                        Directory.Delete(fullPath + ".thumb", true);
                    break;
                }
                catch(Exception)
                {
                    System.Threading.Thread.Sleep(200);
                }
                i++;
            }
            return "ok";
        }
        public int fileid(string path)
        {
            string sql = "select FileID from {databaseOwner}[{objectQualifier}Findy_XsltDb_Files] where FilePath = @FilePath and PortalID = @PortalID";
            using (IDataReader r = Data.ExecuteSQL(sql, new SqlParameter[]{
                new SqlParameter("@FilePath", path),
                new SqlParameter("@PortalID", transformer.DnnSettings.P.PortalID)}))
            {
                if (r.Read())
                    return Convert.ToInt32(r["FileID"]);
            }
            return -1;
        }

        public bool addalias(string alias)
        {
            return addalias(transformer.PortalID, alias);
        }
        public bool addalias(int PortalID, string alias)
        {
            bool IsSuper = transformer.Config.IsSuper;

            if ( !IsSuper && PortalID != transformer.PortalID )
                throw new Exception("Only supermodule can access foreign portals");

            PortalAliasController pac = new PortalAliasController();
            PortalAliasInfo paInfo = new PortalAliasInfo();
            paInfo.PortalID = PortalID;
            paInfo.HTTPAlias = alias;
            pac.AddPortalAlias(paInfo);
            return true;
        }

        public bool deletealias(string alias)
        {
            return deletealias(transformer.PortalID, alias);
        }
        public bool deletealias(int PortalID, string alias)
        {
            bool IsSuper = transformer.Config.IsSuper;

            if (!IsSuper && PortalID != transformer.PortalID)
                throw new Exception("Only supermodule can access foreign portals");

            PortalAliasController pac = new PortalAliasController();
            PortalAliasInfo paInfo = pac.GetPortalAlias(alias, PortalID);
            pac.DeletePortalAlias(paInfo.PortalAliasID);
            return true;
        }

        public bool aliasexists(string alias)
        {
            PortalAliasController pac = new PortalAliasController();
            return pac.GetPortalAliases().Contains(alias);
        }

        public string createdir()
        {
            FileMan m = CreateFileManager();
            return m.BuildInsideDir();
        }

        private static string getExtError(string ext)
        {
            if ( string.IsNullOrEmpty(ext) )
                return "error:wrong-ext: no file extension provided";

            var allowedExts = Host.GetHostSettingsDictionary()["FileExtensions"].Replace(" ", "").ToLower();
            var exts = new List<string>(allowedExts.Split(','));
            if (ext[0] == '.')
                ext = ext.Substring(1);
            if (!exts.Contains(ext))
                return "error:wrong-ext:" + ext;

            return null;
        }

        public string portalmail(string from, string to, string subject, string body)
        {
            return portalmail(from, to, subject, body, true, null);
        }

        public string portalmail(string from, string to, string subject, string body, bool isHtml)
        {
            return portalmail(from, to, subject, body, isHtml, null);
        }

        public string portalmail(string from, string to, string subject, string body, bool isHtml, string _encoding)
        {
            if (string.IsNullOrEmpty(_encoding)) _encoding = "utf-8";
            int codepage;
            var encoding = int.TryParse(_encoding, out codepage)
                               ? Encoding.GetEncoding(codepage)
                               : Encoding.GetEncoding(_encoding);

            var s = XsltDbUtils.GetPortalSettings(PortalController.GetCurrentPortalSettings().PortalId);
            var smtpServer = s.smtpServer;
            var smtpLogin = s.smtpLogin;
            var smtpPassword = s.smtpPassword;
            var smtpEmail = s.noreplyEMail;

            var sUrl = HttpContext.Current.Request.Url.Host.ToString() + HttpContext.Current.Request.Url.AbsolutePath.ToString();
            XPathNavigator nav;

            var sAliasID = PortalController.GetCurrentPortalSettings().PortalAlias.PortalAliasID.ToString();


            if (sAliasID != "")
            {
              /*
                            nav = (XPathNavigator)StaticCache2.Get("all_xsltdb_portal_aliases_settings_" + sAliasID + ":", delegate()
                            {
                              return Data.ExecuteSQL("select * from {databaseOwner}[{objectQualifier}Findy_XsltDb_PortalAliasSettings] WHERE PortalAliasID = " + sAliasID + ";", "alias", null);
                            });
               */
              nav = (XPathNavigator)Data.ExecuteSQL("select * from {databaseOwner}[{objectQualifier}Findy_XsltDb_PortalAliasSettings] WHERE PortalAliasID = " + sAliasID + ";", "alias", null);
              if (nav.Value("//IsPortalSMTP") == "0" || nav.Value("//IsPortalSMTP") == "false")
              {
                smtpServer = nav.Value("//smtpServer");
                smtpLogin = nav.Value("//smtpLogin");
                smtpPassword = nav.Value("//smtpPassword");
                smtpEmail = nav.Value("//smtpEmail");
              }
            }

            from = (from == "auto" ? smtpEmail : from);

            MailMessage msg;
            SmtpClient cli;

            try
            {
              msg = new MailMessage
              {
                From = new MailAddress(from),
                Subject = subject,
                Body = body,
                IsBodyHtml = isHtml,
                ReplyTo = new MailAddress(from),
                BodyEncoding = encoding,
                SubjectEncoding = encoding
              };
              msg.To.Add(new MailAddress(to));

              cli = new SmtpClient(smtpServer)
              {
                Credentials = new NetworkCredential(smtpLogin, smtpPassword)
              };
            }
            catch (Exception ex)
            {
              return "Ошибка отправки письма: server=" + smtpServer + "; from=" + from + ";<br/>ДЕТАЛИ:" + ex.ToString();
            }

            try
            {
              cli.Send(msg);
            }
            catch (Exception ex)
            {
              Data.ExecuteSQL(
                "INSERT INTO {databaseOwner}[{objectQualifier}Findy_XsltDb_EmailMsgList] (date_create, smtpServer, smtpLogin, smtpPassword, smtpEmail, smtpBody, smtpTo, smtpSubject) " +
                "VALUES (GETDATE(), @smtpServer, @smtpLogin, @smtpPassword, @smtpEmail, @smtpBody, @smtpTo, @smtpSubject)",
                new SqlParameter[] {
                    new SqlParameter("@smtpServer",   smtpServer),
                    new SqlParameter("@smtpLogin",    smtpLogin),
                    new SqlParameter("@smtpPassword", smtpPassword),
                    new SqlParameter("@smtpEmail",    from),
                    new SqlParameter("@smtpBody",     body),
                    new SqlParameter("@smtpTo",       to),
                    new SqlParameter("@smtpSubject",  subject)
                }
              );

              return "Ошибка отправки письма: server=" + smtpServer + "; from=" + from + ";<br/>ДЕТАЛИ:" + ex.ToString();
            }

            return "0";
        }

        public string portalmail(XPathNavigator sett){
          return portalmail(sett, CreateNavigator("<root/>"));
        }
        public string portalmail(XPathNavigator sett, XPathNavigator attach)
        {
          string from = sett.Value("//EmailFrom");
          string fromName = sett.Value("//EmailFromName");
          string to = sett.Value("//EmailTo");
          string subject = sett.Value("//EmailSubject");
          string body = sett.Value("//EmailBody");
          string smtpServer = sett.Value("//smtpServer");
          string smtpLogin = sett.Value("//smtpLogin");
          string smtpPassword = sett.Value("//smtpPassword");
          string smtpPort = sett.Value("//smtpPort");
          string smtpEnableSsl = sett.Value("//smtpEnableSsl");
          

          if(fromName != ""){
            fromName = fromName.Replace("\"", "\\\"");
            foreach (char ch in fromName){
              if (ch > '\x007f'){
                fromName.Replace(" ", "_");
                break;
              }
            }
          }

          try
          {
            var msg = new MailMessage
            {
              From = (fromName == "" ? new MailAddress(from) : new MailAddress(from, fromName)),
              Subject = subject,
              Body = body,
              IsBodyHtml = true,
              ReplyTo = new MailAddress(from)
            };
            msg.To.Add(new MailAddress(to));

            XPathNodeIterator nodes;
            nodes = attach.Select("//attachment");
            while (nodes.MoveNext())
            {
              string attachmentURL  = nodes.Current.SelectSingleNode("//URL").Value;
              string attachmentName = nodes.Current.SelectSingleNode("//Name").Value;
              string attachmentMIME = nodes.Current.SelectSingleNode("//MIME").Value;
              if(attachmentURL != ""){
                HttpWebRequest r = (HttpWebRequest) WebRequest.Create(attachmentURL);
                HttpWebResponse resp = (HttpWebResponse) r.GetResponse();
        
                Stream receiveStream = resp.GetResponseStream();
                StreamReader readStream = new StreamReader (receiveStream, Encoding.UTF8);
                Attachment a = new Attachment(readStream.BaseStream, attachmentName, attachmentMIME);
                msg.Attachments.Add(a);
              }
            }

            var cli = new SmtpClient(smtpServer);
            if(smtpPort != ""){
              cli.Port = Convert.ToInt32(smtpPort);
            }
            if(smtpEnableSsl == "true"){
              cli.EnableSsl = true;
            }
            cli.Credentials = new NetworkCredential(smtpLogin, smtpPassword);
            

            cli.Send(msg);
          }
          catch (Exception ex)
          {
            Data.ExecuteSQL(
              "INSERT INTO {databaseOwner}[{objectQualifier}Findy_XsltDb_EmailMsgList] (date_create, smtpServer, smtpLogin, smtpPassword, smtpEmail, smtpBody, smtpTo, smtpSubject, error_text) " +
              "VALUES (GETDATE(), @smtpServer, @smtpLogin, @smtpPassword, @smtpEmail, @smtpBody, @smtpTo, @smtpSubject, @error)",
              new SqlParameter[] {
                    new SqlParameter("@smtpServer",   smtpServer),
                    new SqlParameter("@smtpLogin",    smtpLogin),
                    new SqlParameter("@smtpPassword", smtpPassword),
                    new SqlParameter("@smtpEmail",    from),
                    new SqlParameter("@smtpBody",     body),
                    new SqlParameter("@smtpTo",       to),
                    new SqlParameter("@smtpSubject",  subject),
                    new SqlParameter("@error",        ex.ToString())
                }
            );

            return "Ошибка отправки письма: server=" + smtpServer + "; from=" + from + ";<br/>ДЕТАЛИ:" + ex.ToString();
          }

          return "0";
        }

      
        public string sendallmail()
        {
          string id = "";
          string from = "";
          string to = "";
          string subject = "";
          string body = "";
          string smtpServer = "";
          string smtpLogin = "";
          string smtpPassword = "";
          string smtpEmail = "";
          XPathNavigator clone;
          XPathNavigator nav = (XPathNavigator)Data.ExecuteSQL("select * from {databaseOwner}[{objectQualifier}Findy_XsltDb_EmailMsgList];", "record", null);
          XPathNodeIterator nodes = nav.Select("//record");
          while (nodes.MoveNext())
          {
            clone = nodes.Current.Clone();
            if (clone.MoveToChild("smtpServer", ""))
            {
              smtpServer = clone.Value;
            }
            clone = nodes.Current.Clone();
            if (clone.MoveToChild("smtpLogin", ""))
            {
              smtpLogin = clone.Value;
            }
            clone = nodes.Current.Clone();
            if (clone.MoveToChild("smtpPassword", ""))
            {
              smtpPassword = clone.Value;
            }
            clone = nodes.Current.Clone();
            clone.MoveToChild("smtpEmail", "");
            from = clone.Value;

            clone = nodes.Current.Clone();
            if (clone.MoveToChild("id", ""))
            {
              id = clone.Value;
            }
            clone = nodes.Current.Clone();
            if (clone.MoveToChild("smtpTo", ""))
            {
              to = clone.Value;
            }
            clone = nodes.Current.Clone();
            if (clone.MoveToChild("smtpSubject", ""))
            {
              subject = clone.Value;
            }
            clone = nodes.Current.Clone();
            clone.MoveToChild("smtpBody", "");
            body = clone.Value;

            try
            {
              var msg = new MailMessage
              {
                From = new MailAddress(from),
                Subject = subject,
                Body = body,
                IsBodyHtml = true,
                ReplyTo = new MailAddress(from)
              };
              msg.To.Add(new MailAddress(to));

              var cli = new SmtpClient(smtpServer)
              {
                Credentials = new NetworkCredential(smtpLogin, smtpPassword)
              };

              cli.Send(msg);
              Data.ExecuteSQL(
                "DELETE FROM {databaseOwner}[{objectQualifier}Findy_XsltDb_EmailMsgList] WHERE id=@id",
                new SqlParameter[] {
                    new SqlParameter("@id", id)
                }
              );

            }
            catch (Exception ex)
            {
              Data.ExecuteSQL(
                "UPDATE {databaseOwner}[{objectQualifier}Findy_XsltDb_EmailMsgList] SET error_text = @error " +
                "WHERE id=@id",
                new SqlParameter[] {
                    new SqlParameter("@id",    id),
                    new SqlParameter("@error", ex.ToString())
                }
              );

            }
          }

          string cntDays = Host.GetHostSettingsDictionary()["XsltDb_MailDaysForArchive"];
          if (cntDays != "")
          {
            Data.ExecuteSQL(
              " DECLARE @cntDay int = @cntD;" +
              " DECLARE @curDate datetime = GETDATE();" +
              " INSERT INTO Findy_XsltDb_EmailMsgList_Archive (date_create, smtpServer, smtpLogin, smtpPassword, smtpEmail, smtpBody, smtpTo, smtpSubject, error_text)" +
              " SELECT date_create, smtpServer, smtpLogin, smtpPassword, smtpEmail, smtpBody, smtpTo, smtpSubject, error_text" +
              " FROM Findy_XsltDb_EmailMsgList WHERE @curDate > DATEADD(DAY,@cntDay,date_create);" +
              " DELETE FROM Findy_XsltDb_EmailMsgList WHERE @curDate > DATEADD(DAY,@cntDay,date_create);"
              ,
              new SqlParameter[] {
                    new SqlParameter("@cntD", cntDays)
                }
            );
          }

          return "0";
        }


        public string captcha()
        {
            return Guid.NewGuid().ToString();
        }

        public bool captchavalidate(string id, string value)
        {
            var key = XsltDbCaptcha.CreateCacheKey(id);
            var c = (XsltDbCaptcha)StaticCache2.Get(key);
            if (c == null) return false;
            StaticCache2.Remove(key);
            return string.Compare(c.Text, value, true) == 0;
        }

        public string executexsltask(string xsl){
          Transformer t = new Transformer(-1, -1, -1);
          return t.JustTransform(xsl, "<root/>", false, true);
        }

        public string idndecode(string rf)
        {
          return new IdnMapping().GetUnicode(rf, 0, rf.Length);
        }
        public string idnencode(string rf)
        {
          return new IdnMapping().GetAscii(rf, 0, rf.Length);
        }

        public string getclientid()
        {
            return transformer.ClientID;
        }
/*
        public void addrole(string roleName)
        {
          DotNetNuke.Security.Roles.RoleInfo ri = rc.GetRoleByName(transformer.DnnSettings.P.PortalID, roleName);
          DotNetNuke.Entities.Users.UserInfo userInfo = DotNetNuke.Entities.Users.UserController.GetCurrentUserInfo();
 
          //добавляем роль roleName пользователю на всегда дней
          rc.AddUserRole(transformer.DnnSettings.P.PortalID, userInfo.UserID, ri.RoleID);
 
          // обязательно записываем обновленный провиль в базу данных
          DotNetNuke.Entities.Users.UserController.UpdateUser(transformer.DnnSettings.P.PortalID, userInfo);
        }

        public void addrole(string roleName)
        {
          DotNetNuke.Security.Roles.RoleInfo ri = rc.GetRoleByName(transformer.DnnSettings.P.PortalID, roleName);
          DotNetNuke.Entities.Users.UserInfo userInfo = DotNetNuke.Entities.Users.UserController.GetCurrentUserInfo();

          DeleteUserRole(userInfo.UserID,ri, transformer.DnnSettings.P,false);
          DotNetNuke.Entities.Users.UserController.UpdateUser(transformer.DnnSettings.P.PortalID, userInfo);
        }
*/
    }
}